﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

/// <summary>
/// Summary description for CExcel
/// </summary>
public class CExcel
{

    string _filePath;
    string _sheetName;
    string _sheetRange;

    public CExcel(string filePath, string sheetName)
    {
        _filePath = filePath;
        _sheetName = sheetName;
    }
    public CExcel(string filePath, string sheetName, string sheetRange)
    {
        _filePath = filePath;
        _sheetName = sheetName;
        _sheetRange = sheetRange;
    }



    static String[] GetExcelSheetNames(string excelFile)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;

        try
        {
            // Connection String. Change the excel file to the file you
            // will search.
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
            // Create connection object by using the preceding connection string.
            objConn = new OleDbConnection(connString);
            // Open connection with the database.
            objConn.Open();
            // Get the data table containg the schema guid.
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            // Clean up.
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

    public System.Data.DataTable Read()
    {
        //EXCEL's Connnection 
        string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + this._filePath + "; Extended Properties=Excel 8.0;";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);
        objConn.Open();

        //OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + this._sheetName + "$A4:AX10000]", objConn);
        //OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + this._sheetName +"$"+this._sheetRange+ "]", objConn);
        // OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + this._sheetName.Trim() + "$]", objConn);
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + GetExcelSheetNames(this._filePath)[0] + "]", objConn);
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.SelectCommand = objCmdSelect;

        DataTable dt = new DataTable();

        da.Fill(dt);
        objConn.Close();

        /*
        int RowCount = dt.Rows.Count;
        for(int i=0; i< RowCount; i++)		//	Delete Space Row, MST is key
        {
            if( dt.Rows[i][0].ToString().Trim().Length == 0)
            {
                dt.Rows[i].Delete();
            }
        }
         */

        return dt;
    }

    public DataTable LoadCSV(int numberOfRows)
    {
        DataSet ds = new DataSet();
        try
        {
            // Creates and opens an ODBC connection
            string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _filePath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
            string sql_select;
            OdbcConnection conn;
            conn = new OdbcConnection(strConnString.Trim());
            conn.Open();

            //Creates the select command text
            if (numberOfRows == -1)
            {
                sql_select = "select * from [" + _sheetName + "]";
            }
            else
            {
                sql_select = "select top " + numberOfRows +
                    " * from [" + _sheetName + "]";
            }

            //Creates the data adapter
            OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);

            //Fills dataset with the records from CSV file
            obj_oledb_da.Fill(ds, "csv");

            //closes the connection
            conn.Close();
        }
        catch (Exception e) //Error
        {

        }
        return ds.Tables[0];
    }

    public DataTable GetCSVRows(bool IsFirstRowHeader)
    {
        string header = "No";
        string sql = string.Empty;
        DataTable dataTable = null;
        string pathOnly = string.Empty;
        string fileName = string.Empty;

        try
        {

            pathOnly = Path.GetDirectoryName(_filePath);
            fileName = Path.GetFileName(_filePath);

            sql = @"SELECT * FROM [" + fileName + "]";

            if (IsFirstRowHeader)
            {
                header = "Yes";
            }

            using (OleDbConnection connection = new OleDbConnection(
                    @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                    ";Extended Properties=\"Text;HDR=" + header + "\""))
            {
                using (OleDbCommand command = new OleDbCommand(sql, connection))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                    {
                        dataTable = new DataTable();                     
                        adapter.Fill(dataTable);

                    }
                }
            }
        }
        finally
        {

        }

        return dataTable;

    }



    /*private string ReadTest()
    {
        string str = "";
        // Read sample data from CSV file
        using (CsvFileReader reader = new CsvFileReader(_filePath))
        {
            SoftCircuits.CsvRow row = new SoftCircuits.CsvRow();
            while (reader.ReadRow(row))
            {
                foreach (string s in row)
                {
                    Console.Write(s);
                    Console.Write(" ");
                }
                Console.WriteLine();
            }
        }
        return str;
    }
    */
}
